﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Collections.Specialized;
using System.IO;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.Data;
using System.Data.Sql;
using System.Windows.Forms;
using ScriptifyLibrary20;

namespace ScriptifyLibrary40
{
    public class ScriptifySQLServer : IScriptifyDataAccess
    {

        public List<string> GetServers()
        {
            var results = SqlDataSourceEnumerator.Instance.GetDataSources();
            List<string> s = new List<string>();
            foreach (DataRow row in results.Rows)
            {
                if(String.IsNullOrEmpty(row["InstanceName"].ToString()))
                    s.Add(row["ServerName"].ToString());
                else
                    s.Add(row["ServerName"].ToString() + "/" + row["InstanceName"].ToString());
            }
            return s;
        }

        public List<string> GetDatabases(ScriptifyServer server, IProgressBar bar)
        {
            try
            {
                server.serverName = "tempdb";
                SqlConnection sqlConnection = GetSqlConnection(server);
                Server sqlServer = new Server(new ServerConnection(sqlConnection));
                List<string> databases = new List<string>();

                //Currenctly working for Sql Server 2005 or above
                if (sqlServer.Information.Version.Major >= 9)
                {

                    bar.SetMaximum(sqlServer.Databases.Count);
                    bar.SetValue(0);

                    databases.Add("(Select a database)");

                    foreach (Database db in sqlServer.Databases)
                    {

                        databases.Add(db.Name);
                        bar.SetValue(bar.GetValue()+1);
                        bar.Refresh();
                    }

                }
                else
                {
                    throw new MyMessageException("SMO Scripting is only available for SQL Server 2005 and higher", null);
                }

                return databases;
            }
            catch (ConnectionFailureException ex)
            {
                throw new MyApplicationException("Unable to connect to server", ex.InnerException);               
            }

            
        }

        public List<ScriptifyObject> GetObjects(ScriptifyServer server, IProgressBar bar)
        {
            Server sqlServer = GetServer(server);

            Database db = sqlServer.Databases[server.databaseName];

            List<ScriptifyObject> objects = new List<ScriptifyObject>();
            DataTable dbEnubObjects = db.EnumObjects();

            bar.SetValue(0);
            bar.SetMaximum(dbEnubObjects.Rows.Count);
            bar.RefreshWithText("Getting database tables and views");

            foreach (DataRow row in dbEnubObjects.Rows)
            {
                objects.Add(new ScriptifyObject { Schema = row[1].ToString(), ObjectName = row[2].ToString(), ObjectType = row[0].ToString(), URN = row[3].ToString()});
                bar.SetValue(bar.GetValue() + 1);
                bar.Refresh();
            }

            bar.SetValue(0);
             
            bar.RefreshWithText("Getting database triggers");

            foreach (Table table in db.Tables)
            {
                foreach (Trigger trigger in table.Triggers)
                {
                     objects.Add(new ScriptifyObject { Schema = table.Schema.ToString(), ObjectName = trigger.Name.ToString(), ObjectType = "Trigger", URN = trigger.Urn.ToString()});
                }
            }

            return objects;    
        }

        public StringBuilder ScriptDatabase(ScriptifyServer server, ScriptifyOptions options, List<ScriptifyObject> objects, IProgressBar bar)
        {
            Server sqlServer = GetServer(server);
            StringBuilder sBuilder = new StringBuilder();

            bar.SetValue(0);
            bar.SetMaximum(objects.Count);

            //if (File.Exists(txtSaveLocation.Text))
            //    File.Delete(txtSaveLocation.Text);

            ScriptingOptions scriptingOptions = new ScriptingOptions();
            scriptingOptions.IncludeHeaders = options.includeHeaders;
            scriptingOptions.Indexes = options.includeIndexes;
            scriptingOptions.DriAllKeys = options.includeKeys;
            scriptingOptions.NoCollation = !options.includeCollation;
            scriptingOptions.SchemaQualify = options.qualifySchema;
            scriptingOptions.SchemaQualifyForeignKeysReferences = options.qualifyForeignKeysSchema;
            scriptingOptions.Permissions = options.includePermissions;
            scriptingOptions.Encoding = (options.isUnicode) ? Encoding.Unicode : Encoding.ASCII;

            ScriptingOptions dropOptions = new ScriptingOptions();
            dropOptions.ScriptDrops = options.includeDrops;
            dropOptions.IncludeIfNotExists = options.includeIfNotExists; ;
            dropOptions.SchemaQualify = options.qualifySchema;
            dropOptions.Encoding = (options.isUnicode) ? Encoding.Unicode : Encoding.ASCII;

            if (options.generateFiles)
            {
                scriptingOptions.FileName = dropOptions.FileName = options.folderLocation;
                scriptingOptions.AppendToFile = dropOptions.AppendToFile = true;
            }

            

            Scripter scripter = new Scripter(sqlServer);
            Urn[] urn = new Urn[1];

            foreach (ScriptifyObject obj in objects)
            {

                if (options.multipleFiles)
                {
                    scriptingOptions.FileName = dropOptions.FileName = "\\" + FileNameHelper(obj, options);

                    if (File.Exists(scriptingOptions.FileName))
                        File.Delete(scriptingOptions.FileName);

                }
                else
                    scriptingOptions.FileName = dropOptions.FileName = options.folderLocation + "\\" + options.folderNaming + ".sql";

                bar.SetValue(bar.GetValue() + 1);
                bar.RefreshWithText("Scripting: " + obj.ObjectName + "... Please wait");

                if (obj.URN != null)
                    urn[0] = obj.URN;
                else
                    urn[0] = "Server[@Name='" + server.serverName.ToUpper() + "']/Database[@Name='" + server.databaseName.ToUpper() + "']/" + obj.ObjectType + "[@Name='" + obj.ObjectName + "' and @Schema='" + obj.Schema + "']";

                if (options.includeDrops)
                {
                    scripter.Options = dropOptions;
                    ConcatLines(scripter.Script(urn), sBuilder);
                    ConcatGo(sBuilder);
                }

                if (options.includeCreates)
                {
                    scripter.Options = scriptingOptions;
                    ConcatLines(scripter.Script(urn), sBuilder);
                    ConcatGo(sBuilder);
                }

            }


            scripter = null;
            urn = null;

            return sBuilder;
        }

        /* PRIVATE */
        private static SqlConnection GetSqlConnection(ScriptifyServer server)
        {
            SqlConnectionStringBuilder sqlConnectionString = new SqlConnectionStringBuilder();
            sqlConnectionString.DataSource = server.serverName;
            if (server.isWindowsAuthentication)
            {
                sqlConnectionString.IntegratedSecurity = true;
            }
            else
            {
                sqlConnectionString.IntegratedSecurity = false;
                sqlConnectionString.UserID = server.username;
                sqlConnectionString.Password = server.password;
            }
            sqlConnectionString.InitialCatalog = server.databaseName;

            SqlConnection sqlConnection = new SqlConnection(sqlConnectionString.ConnectionString);
            return sqlConnection;

        }

        private static Server GetServer(ScriptifyServer server)
        {
            if (server.isWindowsAuthentication)
                return new Server(server.serverName);
            else
                return new Server(new ServerConnection(server.serverName, server.username, server.password));
        }

        private static void ConcatLines(StringCollection strings, StringBuilder sBuilder)
        {
            foreach (string s in strings)
            {
                sBuilder.Append(System.Environment.NewLine + s);

                if (s.StartsWith("SET QUOTED_IDENTIFIER") || s.StartsWith("SET ANSI_NULLS"))
                    sBuilder.Append(System.Environment.NewLine + "GO");
            }


        }

        private static void ConcatGo(StringBuilder sBuilder)
        {
            sBuilder.Append(System.Environment.NewLine + "GO" + System.Environment.NewLine + System.Environment.NewLine);
        }

        private static string FileNameHelper(ScriptifyObject obj, ScriptifyOptions opt)
        {

            string extension = ".sql";
            if (opt.namingConvention)
            {
                switch (obj.ObjectType)
                {
                    case "StoredProcedure":
                        extension = ".prc";
                        break;
                    case "UserDefinedFunction":
                        extension = ".udf";
                        break;
                    case "View":
                        extension = ".viw";
                        break;
                    case "Table":
                        extension = ".tab";
                        break;
                    case "Trigger":
                        extension = ".trg";
                        break;
                    default:
                        extension = ".sql";
                        break;
                }
            }

            if (obj.Schema.Length > 0)
                return Path.Combine(opt.folderLocation, obj.Schema + "." + obj.ObjectName + extension);
            else
                return Path.Combine(opt.folderLocation, obj.ObjectName + extension);
        }
    }
}
